package com.stps.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import com.stps.dao.TextBookDao;
import com.stps.vo.TextBook;
import com.mysql.cj.util.StringUtils;

public class TextBookDaoImpl implements TextBookDao{
	private Connection conn; // 需要利用Connection对象操作
	private PreparedStatement pstmt;

	/**
	*如果要想使用数据层进行原子性的功能操作实现，必须要提供有Connection接口对象<br>
	*另外，由于开发之中业务层要调用数据层，所以数据库的打开与关闭交由业务层处理
	*@param conn表示数据库连接对象
	*/

	public TextBookDaoImpl(Connection conn) {
		this.conn = conn;
	}

	public boolean doCreate(TextBook vo) throws Exception {
		String sql = "INSERT INTO textbook(id,name,num) VALUES(?,?,?);";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, vo.getId());
		this.pstmt.setString(2, vo.getName());
		this.pstmt.setInt(3, vo.getNum());
		return this.pstmt.executeUpdate() > 0;
	}

	public boolean doUpdate(TextBook vo) throws Exception {
		String sql = "UPDATE textbook SET name=?,num=? WHERE id=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, vo.getName());
		this.pstmt.setInt(2, vo.getNum());
		this.pstmt.setInt(3, vo.getId());
		return this.pstmt.executeUpdate() > 0;
	}

	public boolean doRemoveBatch(Integer ids) throws Exception {
		if (ids == null) {// 没有要删除的数据
			return false;
		}
		String sql = "DELETE FROM textbook WHERE id = ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, ids);
		return this.pstmt.executeUpdate() == 1;
	}

	public TextBook findById(Integer id) throws Exception {
		TextBook vo = null;
		String sql = "SELECT name,num FROM textbook WHERE id=?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, id);
		ResultSet rs = this.pstmt.executeQuery();
		if (rs.next()) {
			vo = new TextBook();
			vo.setName(rs.getString("name"));
			vo.setNum(rs.getInt("num"));
		}
		return vo;
	}

	public List<TextBook> findAll() throws Exception {
		List<TextBook> all = new ArrayList<TextBook>();
		String sql = "SELECT id,name,num FROM textbook ";
		this.pstmt = this.conn.prepareStatement(sql);
		ResultSet rs = this.pstmt.executeQuery();
		while (rs.next()) {
			TextBook vo = new TextBook();
			vo.setId(rs.getInt("id"));
			vo.setName(rs.getString("name"));
			vo.setNum(rs.getInt("num"));
			all.add(vo);
		}
		return all;

	}

	public List<TextBook> findAllSplit(Integer currentPage, Integer lineSize, String column, String keyWord)
			throws Exception {
		List<TextBook> all = new ArrayList<TextBook>();
		String sql = "SELECT id,name,num  FROM textbook  " + "WHERE ? "
				+ " LIKE ?   limit ?,?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, column);
		this.pstmt.setString(2, "%" + keyWord + "%");
		this.pstmt.setInt(3, (currentPage - 1) * lineSize);
		this.pstmt.setInt(4, lineSize);
		ResultSet rs = this.pstmt.executeQuery();
		while (rs.next()) {
			TextBook vo = new TextBook();
			vo.setId(rs.getInt("id"));
			vo.setName(rs.getString("name"));
			vo.setNum(rs.getInt("num"));
			all.add(vo);
		}
		return all;
	}

	public List<TextBook> findAllSplit(Integer currentPage, Integer lineSize, TextBook TextBook) {
		List<TextBook> all = new ArrayList<TextBook>();
		String sql = "SELECT id,name,num " + " FROM textbook  ";
		if (TextBook != null) {
			// 处理非空的条件子句
			// //json格式：{"name":"1","sex":"2","dept":"3","major":"4","nativePlace":"5"}
			sql = sql + " WHERE 1=1 ";
			if (TextBook.getId() != 0)
				sql = sql + " and  id =" + TextBook.getId();
			if (!StringUtils.isEmptyOrWhitespaceOnly(TextBook.getName()))
				sql = sql + " and  name like  '" + TextBook.getName() + "%'";
			if (TextBook.getNum() != null)
				sql = sql + " and  num like  '" + TextBook.getNum() + "%'";
		}
		sql = sql + "   limit ?,?";
		try {
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, (currentPage - 1) * lineSize);
			this.pstmt.setInt(2, lineSize);
			ResultSet rs = this.pstmt.executeQuery();
			while (rs.next()) {
				TextBook vo = new TextBook();
				vo.setId(rs.getInt("id"));
				vo.setName(rs.getString("name"));
				vo.setNum(rs.getInt("num"));
				all.add(vo);
			}
			return all;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public Integer getAllCount(TextBook TextBook) throws Exception {
		String sql = "SELECT COUNT(id) FROM textbook ";

		if (TextBook != null) {
			// 处理非空的条件子句
			// //json格式：{"name":"1","sex":"2","dept":"3","major":"4","nativePlace":"5"}
			sql = sql + " WHERE 1=1 ";
			if (TextBook.getId() != 0)
				sql = sql + " and  id =" + TextBook.getId();
			if (!StringUtils.isEmptyOrWhitespaceOnly(TextBook.getName()))
				sql = sql + " and  name like  '" + TextBook.getName() + "%'";
			if (TextBook.getNum() != null)
				sql = sql + " and  num like  '" + TextBook.getNum() + "%'";
		}
		this.pstmt = this.conn.prepareStatement(sql);
		ResultSet rs = this.pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		}
		return 0;
	}

	public Integer getAllCount(String column, String keyWord) throws Exception {
		String sql = "SELECT COUNT(id) FROM textbook  WHERE " + column + " LIKE  ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, "%" + keyWord + "%");
		ResultSet rs = this.pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		}
		return 0;
	}

}
